package com.pokermind.repository.dao.impl;

import com.pokermind.model.po.PokerDecisionSnapshot;
import com.pokermind.repository.dao.PokerDecisionSnapshotDao;
import com.pokermind.repository.mapper.PokerDecisionSnapshotRowMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * PokerDecisionSnapshot DAO实现类
 * 使用JdbcTemplate实现AI决策快照数据访问操作
 * 
 * @author PokerMind Team
 */
@Repository
public class PokerDecisionSnapshotDaoImpl implements PokerDecisionSnapshotDao {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    private final PokerDecisionSnapshotRowMapper rowMapper = new PokerDecisionSnapshotRowMapper();
    
    @Override
    public PokerDecisionSnapshot save(PokerDecisionSnapshot snapshot) {
        String sql = """
            INSERT INTO poker_decision_snapshots (
                user_id, hand_id, snapshot_sequence, game_state, ai_recommendation,
                ai_confidence, ai_reasoning, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """;
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setLong(1, snapshot.getUserId());
            ps.setLong(2, snapshot.getHandId());
            ps.setInt(3, snapshot.getSnapshotSequence());
            ps.setString(4, snapshot.getGameState());
            ps.setString(5, snapshot.getAiRecommendation());
            ps.setBigDecimal(6, snapshot.getAiConfidence());
            ps.setString(7, snapshot.getAiReasoning());
            ps.setString(8, snapshot.getCreatedAt() != null ? 
                snapshot.getCreatedAt().toString() : LocalDateTime.now().toString());
            return ps;
        }, keyHolder);
        
        Number generatedId = keyHolder.getKey();
        if (generatedId != null) {
            snapshot.setId(generatedId.longValue());
        }
        
        return snapshot;
    }
    
    @Override
    public int batchSave(List<PokerDecisionSnapshot> snapshots) {
        String sql = """
            INSERT INTO poker_decision_snapshots (
                user_id, hand_id, snapshot_sequence, game_state, ai_recommendation,
                ai_confidence, ai_reasoning, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """;
        
        return jdbcTemplate.batchUpdate(sql, snapshots, snapshots.size(), (ps, snapshot) -> {
            ps.setLong(1, snapshot.getUserId());
            ps.setLong(2, snapshot.getHandId());
            ps.setInt(3, snapshot.getSnapshotSequence());
            ps.setString(4, snapshot.getGameState());
            ps.setString(5, snapshot.getAiRecommendation());
            ps.setBigDecimal(6, snapshot.getAiConfidence());
            ps.setString(7, snapshot.getAiReasoning());
            ps.setString(8, snapshot.getCreatedAt() != null ? 
                snapshot.getCreatedAt().toString() : LocalDateTime.now().toString());
        }).length;
    }
    
    @Override
    public Optional<PokerDecisionSnapshot> findById(Long id) {
        String sql = "SELECT * FROM poker_decision_snapshots WHERE id = ?";
        try {
            PokerDecisionSnapshot snapshot = jdbcTemplate.queryForObject(sql, rowMapper, id);
            return Optional.ofNullable(snapshot);
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty();
        }
    }
    
    @Override
    public List<PokerDecisionSnapshot> findAll() {
        String sql = "SELECT * FROM poker_decision_snapshots ORDER BY created_at DESC";
        return jdbcTemplate.query(sql, rowMapper);
    }
    
    @Override
    public int update(PokerDecisionSnapshot snapshot) {
        String sql = """
            UPDATE poker_decision_snapshots SET 
                user_id = ?, hand_id = ?, snapshot_sequence = ?, game_state = ?,
                ai_recommendation = ?, ai_confidence = ?, ai_reasoning = ?
            WHERE id = ?
        """;
        
        return jdbcTemplate.update(sql,
                snapshot.getUserId(), snapshot.getHandId(), snapshot.getSnapshotSequence(),
                snapshot.getGameState(), snapshot.getAiRecommendation(), snapshot.getAiConfidence(),
                snapshot.getAiReasoning(), snapshot.getId()
        );
    }
    
    @Override
    public int deleteById(Long id) {
        String sql = "DELETE FROM poker_decision_snapshots WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }
    
    @Override
    public int delete(PokerDecisionSnapshot snapshot) {
        return deleteById(snapshot.getId());
    }
    
    @Override
    public boolean existsById(Long id) {
        String sql = "SELECT COUNT(1) FROM poker_decision_snapshots WHERE id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, id);
        return count != null && count > 0;
    }
    
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM poker_decision_snapshots";
        Long count = jdbcTemplate.queryForObject(sql, Long.class);
        return count != null ? count : 0L;
    }
    
    @Override
    public List<PokerDecisionSnapshot> findByHandId(Long handId) {
        String sql = "SELECT * FROM poker_decision_snapshots WHERE hand_id = ? ORDER BY snapshot_sequence";
        return jdbcTemplate.query(sql, rowMapper, handId);
    }
    
    @Override
    public List<PokerDecisionSnapshot> findByUserId(Long userId, int limit) {
        String sql = "SELECT * FROM poker_decision_snapshots WHERE user_id = ? ORDER BY created_at DESC LIMIT ?";
        return jdbcTemplate.query(sql, rowMapper, userId, limit);
    }
    
    @Override
    public Optional<PokerDecisionSnapshot> findByHandIdAndSequence(Long handId, Integer snapshotSequence) {
        String sql = "SELECT * FROM poker_decision_snapshots WHERE hand_id = ? AND snapshot_sequence = ?";
        try {
            PokerDecisionSnapshot snapshot = jdbcTemplate.queryForObject(sql, rowMapper, handId, snapshotSequence);
            return Optional.ofNullable(snapshot);
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty();
        }
    }
    
    @Override
    public List<PokerDecisionSnapshot> findByUserIdAndTimeRange(Long userId, LocalDateTime startTime, LocalDateTime endTime) {
        String sql = "SELECT * FROM poker_decision_snapshots WHERE user_id = ? AND created_at >= ? AND created_at <= ? ORDER BY created_at DESC";
        return jdbcTemplate.query(sql, rowMapper, userId, startTime.toString(), endTime.toString());
    }
    
    @Override
    public List<PokerDecisionSnapshot> findWithAiRecommendation(Long userId, int limit) {
        String sql = userId != null 
            ? "SELECT * FROM poker_decision_snapshots WHERE ai_recommendation IS NOT NULL AND user_id = ? ORDER BY created_at DESC LIMIT ?"
            : "SELECT * FROM poker_decision_snapshots WHERE ai_recommendation IS NOT NULL ORDER BY created_at DESC LIMIT ?";
        
        return userId != null 
            ? jdbcTemplate.query(sql, rowMapper, userId, limit)
            : jdbcTemplate.query(sql, rowMapper, limit);
    }
    
    @Override
    public List<PokerDecisionSnapshot> findHighConfidenceRecommendations(Long userId, BigDecimal minConfidence, int limit) {
        String sql = userId != null 
            ? "SELECT * FROM poker_decision_snapshots WHERE ai_confidence >= ? AND user_id = ? ORDER BY ai_confidence DESC, created_at DESC LIMIT ?"
            : "SELECT * FROM poker_decision_snapshots WHERE ai_confidence >= ? ORDER BY ai_confidence DESC, created_at DESC LIMIT ?";
        
        return userId != null 
            ? jdbcTemplate.query(sql, rowMapper, minConfidence, userId, limit)
            : jdbcTemplate.query(sql, rowMapper, minConfidence, limit);
    }
    
    @Override
    public long countByUserId(Long userId) {
        String sql = "SELECT COUNT(*) FROM poker_decision_snapshots WHERE user_id = ?";
        Long count = jdbcTemplate.queryForObject(sql, Long.class, userId);
        return count != null ? count : 0L;
    }
    
    @Override
    public long countByHandId(Long handId) {
        String sql = "SELECT COUNT(*) FROM poker_decision_snapshots WHERE hand_id = ?";
        Long count = jdbcTemplate.queryForObject(sql, Long.class, handId);
        return count != null ? count : 0L;
    }
    
    @Override
    public Optional<PokerDecisionSnapshot> findLatestByUserId(Long userId) {
        String sql = "SELECT * FROM poker_decision_snapshots WHERE user_id = ? ORDER BY created_at DESC LIMIT 1";
        try {
            PokerDecisionSnapshot snapshot = jdbcTemplate.queryForObject(sql, rowMapper, userId);
            return Optional.ofNullable(snapshot);
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty();
        }
    }
    
    @Override
    public int deleteByHandId(Long handId) {
        String sql = "DELETE FROM poker_decision_snapshots WHERE hand_id = ?";
        return jdbcTemplate.update(sql, handId);
    }
    
    @Override
    public List<PokerDecisionSnapshot> findByRecommendation(String recommendation, Long userId, int limit) {
        String sql = userId != null 
            ? "SELECT * FROM poker_decision_snapshots WHERE ai_recommendation = ? AND user_id = ? ORDER BY created_at DESC LIMIT ?"
            : "SELECT * FROM poker_decision_snapshots WHERE ai_recommendation = ? ORDER BY created_at DESC LIMIT ?";
        
        return userId != null 
            ? jdbcTemplate.query(sql, rowMapper, recommendation, userId, limit)
            : jdbcTemplate.query(sql, rowMapper, recommendation, limit);
    }
}